大家好，今天來介紹如何計算一組資料中，某筆資料與上一筆的差值，例如計算當月與上個月的金額差多少。

計算方式

假設我們要計算每個月存款的差異，先建立每月存款資料表與測試資料。

CREATE TABLE MonthSaving(
	YearMonth VARCHAR(7),
	Saving MONEY
)

INSERT INTO MonthSaving (YearMonth, Saving)
VALUES ('2022-08', 10060), ('2022-09', 10236), ('2022-10', 10361), ('2022-11', 10765), ('2022-12', 10987)

首先，先依照需要的欄位排序並呼叫 ROW_NUMBER() 取得序號欄位，此處為 YearMonth 欄位為例。

SELECT YearMonth, Saving
	, ROW_NUMBER() OVER (ORDER BY YearMonth ASC) AS ID
FROM MonthSaving M

將上述的資料存入暫存表，或寫為 CTE，此處寫為 CTE。

WITH MonthSavingOrder (YearMonth, Saving, ID)
AS
(
	SELECT YearMonth, Saving
    	, ROW_NUMBER() OVER (ORDER BY YearMonth ASC) AS ID
	FROM MonthSaving M
)

從原本資料表取得所有資料，並使用 INNER JOIN 依照識別欄位連接暫存表或 CTE (此處使用 YearMonth)，此暫存表再用 LEFT JOIN 連接自己，但序號 + 1。然後再計算兩個暫存表的差異即可。

SELECT MS.YearMonth, MS.Saving, MSO.Saving - MSO2.Saving AS Income
FROM MonthSaving MS
INNER JOIN MonthSavingOrder MSO ON MS.YearMonth = MSO.YearMonth
LEFT JOIN MonthSavingOrder MSO2 ON MSO.ID = (MSO2.ID + 1)

上面的範例將計算上個月到本月間存入的金額，並命名為 Income 欄位。

最後要留意，如果遇到 NULL 欄位時，要如何處理。

參考資料

• [進階SQL]With As進行子查詢(CTE)[SQL-004] - jimmy-wang - Medium
• 另一種做法: [SQL]計算與上一筆的差值 - topcat 姍舞之間的極度凝聚 - 點部落

完整範例

CREATE TABLE MonthSaving(
	YearMonth VARCHAR(7),
	Saving MONEY
)

INSERT INTO MonthSaving (YearMonth, Saving)
VALUES ('2022-08', 10060), ('2022-09', 10236), ('2022-10', 10361), ('2022-11', 10765), ('2022-12', 10987)
;
WITH MonthSavingOrder (YearMonth, Saving, ID)
AS
(
	SELECT YearMonth, Saving
    	, ROW_NUMBER() OVER (ORDER BY YearMonth ASC) AS ID
	FROM MonthSaving M
)
SELECT MS.YearMonth, MS.Saving, MSO.Saving - MSO2.Saving AS Income
FROM MonthSaving MS
INNER JOIN MonthSavingOrder MSO ON MS.YearMonth = MSO.YearMonth
LEFT JOIN MonthSavingOrder MSO2 ON MSO.ID = (MSO2.ID + 1)
;
DROP TABLE MonthSaving